/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package org.dusin.teachassistdbservice.dao4mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
import org.dusin.teachassistdbservice.dao.AttendanceDao;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author dusin
 */
public class AttendanceImpl implements AttendanceDao{
    static Logger logger = Logger.getLogger(AttendanceImpl.class);
    private JdbcTemplate jdbcTemplate;
    
    public AttendanceImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }
    
    
    @Override
    public boolean doCreate(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "insert into attendance(teachclassid,no,note,createTime) values(?,?,?,?)";
        logger.debug(sql+"?"+bean);
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
     //返回自增长ID
    @Override
    public long doCreateWithGetId(final Object[] bean) {
        long flag = -1;
        final String sql = "insert into attendance(teachclassid,no,note,createTime) values(?,?,?,?)";
        logger.debug(sql+"?"+bean);
        KeyHolder keyHolder = new GeneratedKeyHolder();
     
        int rows = jdbcTemplate.update(new PreparedStatementCreator(){

            @Override
            public PreparedStatement createPreparedStatement(Connection con)
                    throws SQLException {
                 PreparedStatement ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);                 
                 ps.setInt(1, (Integer)bean[0]);
                 ps.setInt(2, (Integer)bean[1]);
                 ps.setString(3, (String)bean[2]);
                 ps.setString(4, (String)bean[3]);                 
                 return ps;
            }
        }, keyHolder);
        
        if (rows > 0) {
            flag = keyHolder.getKey().longValue();
        }
        return flag;
    }
    
    @Override
    public boolean doCreateAttendance(Object[] bean){
        boolean flag = true;
        String sql = null;
        sql = "insert into attendancedetail(attendanceId,studentno,status,createtime,note) values(?,?,?,?,?)";
        logger.debug(sql+"?"+bean);
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    /**
     *
     * @param bean
     * @return
     */
    @Override
    public boolean doCreateAttendance(List<Object[]> bean){
        boolean flag = true;
        String sql = null;
        sql = "insert into attendancedetail(attendanceId,studentno,status,createtime,note) values(?,?,?,?,?)";
        logger.debug(sql+"?"+bean);
        int[] rows = jdbcTemplate.batchUpdate(sql, bean);        
        return flag;
    }
    
    @Override
    public boolean isScoreExisted(int attendanceId,String studentNo){
        String sql = null;
        sql = "select count(*) from attendancedetail where attendanceId= ? and studentNo=?";
        int c =  jdbcTemplate.queryForObject(sql,Integer.class,attendanceId,studentNo);
        return c>0?true:false;
    }
    
   
   
    //修改状态 status==3 
    @Override
    public boolean doSetStatus(int attendanceId,String studentNo,int status,String note){
        boolean flag = true;
        String sql = null;
        sql = "update attendancedetail set status=?,note=?  where attendanceId=? and studentno=? ";
        int rows = jdbcTemplate.update(sql, status,note,attendanceId,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doDeleteByID(int id) {
        boolean flag = true;
        String sql = null;
        sql = "delete from attendance where id = ?";
        logger.debug(sql);
        int rows = jdbcTemplate.update(sql, id);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doDeleteDetail(int teachclassid,String studentNo) {
        boolean flag = true;
        String sql = null;
        sql = "delete from attendancedetail where attendanceId = ? and studentNo=?";
        logger.debug(sql);
        int rows = jdbcTemplate.update(sql, teachclassid,studentNo);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doDeleteDetail(List<Object[]> beans) {
        boolean flag = true;
        String sql = null;
        sql = "delete from attendancedetail where attendanceId = ? and studentNo=?";
        logger.debug(sql);
        int[] rows = jdbcTemplate.batchUpdate(sql, beans);        
        return flag;
    }
    
    @Override
    public boolean doUpdate(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "update attendance set no=?,note=?,createTime=? where id = ?";
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }

    @Override
    public boolean doUpdateDetail(Object[] bean) {
        boolean flag = true;
        String sql = null;
        sql = "update attendancedetail set status=?,note=? where teachclassid = ? and studentNo=?";
        int rows = jdbcTemplate.update(sql, bean);
        if (rows <= 0) {
            flag = false;
        }
        return flag;
    }
    
    @Override
    public boolean doUpdateDetail(List<Object[]> beans) {
        boolean flag = true;
        String sql = null;
        sql = "update attendancedetail set status=?,note=? where teachclassid = ? and studentNo=?";
        int[] rows = jdbcTemplate.batchUpdate(sql, beans);        
        return flag;
    }
    
    @Override
    public Map<String, Object> findByNo(String no) {
        String sql = null;
        sql = "select * from teachclass where teachclassno=?";
        return jdbcTemplate.queryForMap(sql,no);
    }

    @Override
    public List<Map<String, Object>> findAll() {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Map<String, Object>> findAll(int start, int size) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }

    @Override
    public List<Map<String, Object>> findAllByTeachClassId(int teachClassId) {
        String sql = null;
        sql = "select * from attendance where teachclassid=?  order by no";
        return jdbcTemplate.queryForList(sql,teachClassId);
    }
    
    /*
    *查找某门课程某个学生的考勤记录
    */
    @Override
    public List<Map<String,Object>> findAllAttendance4Student(int teachClassId,String studentNo){
        String sql = null;
        sql = "select t1.id as id, no,createtime,status ,note  from  (select attendance.id as id, no,createtime from attendance where teachclassid=? )  t1 left join  (select attendanceid, status ,note  from attendancedetail where  studentNo =?) t2  on id = attendanceid order by no";
        return jdbcTemplate.queryForList(sql,teachClassId,studentNo);
    }
    
    /**
     * 查找某个课程考勤次数
     */
    @Override
    public int findCount(int teachclassId){
        String sql =" select count(*) from attendance where teachclassid= ?";
        return jdbcTemplate.queryForObject(sql,Integer.class,teachclassId);
    }
    
    /**
     * 查找某个课程的所有考勤记录
     */
    @Override
    public List<Map<String,Object>> findAllAttendanceDetail(int teachclassId){
        String sql = "select * from attendancedetail where attendanceid in (select id from attendance where teachclassid = ?)";
        return jdbcTemplate.queryForList(sql,teachclassId);
    }
   
}
